import pandas as pd
import os

# file1和file2两个文件的路径
file1_path = './data/file1.xlsx'
files2_path = './data/base'

# 读取第一个文件
try:
    df1 = pd.read_excel(file1_path)
except FileNotFoundError:
    raise FileNotFoundError(f"File {file1_path} not found.")
except Exception as e:
    raise Exception(f"Error reading {file1_path}: {e}")

# 检查 df1 是否包含 'company_name' 列
if 'company_name' not in df1.columns:
    raise ValueError("Table 1 must have a 'company_name' column.")

# 初始化一个空 DataFrame 来存储最终结果
final_merged_df = df1.copy()

# 遍历 files2_path 中的所有文件
for file2_name in os.listdir(files2_path):
    file2_path = os.path.join(files2_path, file2_name)

    # 检查文件是否为 Excel 文件
    if not file2_name.lower().endswith(('.xlsx', '.xls')):
        print(f"Skipping non-Excel file: {file2_name}")
        continue

    try:
        df2 = pd.read_excel(file2_path)
    except FileNotFoundError:
        print(f"File {file2_path} not found. Skipping...")
        continue
    except Exception as e:
        print(f"Error reading {file2_path}: {e}. Skipping...")
        continue

    # 打印正在处理的文件名
    print(f"Processing file: {file2_name}")

    # 假设 df2 的列名是 '0' 和 '2'，如果不是，请调整列名
    if {'0', '2'}.issubset(set(df2.columns)):
        df2.rename(columns={'0': 'company_name', '2': 'Product_Service'}, inplace=True)
    else:
        print(f"Columns '0' and '2' not found in {file2_name}. Skipping...")
        continue

    # 检查 df2 是否包含所需的列
    if 'company_name' not in df2.columns:
        print(f"'company_name' column not found in {file2_name}. Skipping...")
        continue
    if 'Product_Service' not in df2.columns:
        print(f"'Product_Service' column not found in {file2_name}. Skipping...")
        continue

    # 合并数据
    final_merged_df = pd.merge(final_merged_df, df2[['company_name', 'Product_Service']], on='company_name', how='left')

# 保存最终合并后的结果
output_file_path = './data/merged_file3.xlsx'
final_merged_df.to_excel(output_file_path, index=False)
print(f"Merged data saved to {output_file_path}")